package com.wutuo.aggragation.expire.dao;

import cn.apestar.core.database.BaseMapper;
import com.wutuo.aggragation.expire.domain.ExpireManagement;
import com.wutuo.aggragation.expire.dto.RosterExpireManagementLatestDto;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import java.util.Date;
import java.util.List;

public interface ExpireManagementMapper extends BaseMapper {
    @Update({
            "update wt_expire_management",
            "set del_flag = '0',",
            "update_by = #{updateUserId},",
            "update_date = #{updateDate}",
            "where id = #{id,jdbcType=BIGINT}"
    })
    int deleteByPrimaryKey(@Param("id") Long id, @Param("updateUserId") Long updateUserId, @Param("updateDate") Date updateDate);

    @Insert({
            "insert into wt_expire_management (id, create_by, ",
            "create_date, update_by, ",
            "update_date, remarks, ",
            "del_flag, institution_id, ",
            "roster_id, expire_date, ",
            "start_date, fee)",
            "values (#{id,jdbcType=BIGINT}, #{createBy,jdbcType=BIGINT}, ",
            "#{createDate,jdbcType=TIMESTAMP}, #{updateBy,jdbcType=BIGINT}, ",
            "#{updateDate,jdbcType=TIMESTAMP}, #{remarks,jdbcType=VARCHAR}, ",
            "#{delFlag,jdbcType=CHAR}, #{institutionId,jdbcType=BIGINT}, ",
            "#{rosterId,jdbcType=BIGINT}, #{expireDate,jdbcType=DATE}, ",
            "#{startDate,jdbcType=DATE}, #{fee,jdbcType=DECIMAL})"
    })
    @SelectKey(before = false, keyProperty = "id", resultType = Long.class, statement = "SELECT LAST_INSERT_ID() AS id")
    int insert(ExpireManagement record);

    @InsertProvider(type = ExpireManagementSqlProvider.class, method = "insertSelective")
    int insertSelective(ExpireManagement record);

    @Select({
            "select",
            "id, create_by, create_date, update_by, update_date, remarks, del_flag, institution_id, ",
            "roster_id, expire_date, start_date, fee",
            "from wt_expire_management",
            "where id = #{id,jdbcType=BIGINT}"
    })
    @Results({
            @Result(column = "id", property = "id", jdbcType = JdbcType.BIGINT, id = true),
            @Result(column = "create_by", property = "createBy", jdbcType = JdbcType.BIGINT),
            @Result(column = "create_date", property = "createDate", jdbcType = JdbcType.TIMESTAMP),
            @Result(column = "update_by", property = "updateBy", jdbcType = JdbcType.BIGINT),
            @Result(column = "update_date", property = "updateDate", jdbcType = JdbcType.TIMESTAMP),
            @Result(column = "remarks", property = "remarks", jdbcType = JdbcType.VARCHAR),
            @Result(column = "del_flag", property = "delFlag", jdbcType = JdbcType.CHAR),
            @Result(column = "institution_id", property = "institutionId", jdbcType = JdbcType.BIGINT),
            @Result(column = "roster_id", property = "rosterId", jdbcType = JdbcType.BIGINT),
            @Result(column = "expire_date", property = "expireDate", jdbcType = JdbcType.DATE),
            @Result(column = "start_date", property = "startDate", jdbcType = JdbcType.DATE),
            @Result(column = "fee", property = "fee", jdbcType = JdbcType.DECIMAL)
    })
    ExpireManagement selectByPrimaryKey(Long id);

    @UpdateProvider(type = ExpireManagementSqlProvider.class, method = "updateByPrimaryKeySelective")
    int updateByPrimaryKeySelective(ExpireManagement record);

    @Update({
            "update wt_expire_management",
            "set create_by = #{createBy,jdbcType=BIGINT},",
            "create_date = #{createDate,jdbcType=TIMESTAMP},",
            "update_by = #{updateBy,jdbcType=BIGINT},",
            "update_date = #{updateDate,jdbcType=TIMESTAMP},",
            "remarks = #{remarks,jdbcType=VARCHAR},",
            "del_flag = #{delFlag,jdbcType=CHAR},",
            "institution_id = #{institutionId,jdbcType=BIGINT},",
            "roster_id = #{rosterId,jdbcType=BIGINT},",
            "expire_date = #{expireDate,jdbcType=DATE},",
            "start_date = #{startDate,jdbcType=DATE},",
            "fee = #{fee,jdbcType=DECIMAL}",
            "where id = #{id,jdbcType=BIGINT}"
    })
    int updateByPrimaryKey(ExpireManagement record);


    @SelectProvider(type = ExpireManagementSqlProvider.class, method = "listByRosterId")
    @Results({
            @Result(column = "id", property = "id", jdbcType = JdbcType.BIGINT, id = true),
            @Result(column = "create_by", property = "createBy", jdbcType = JdbcType.BIGINT),
            @Result(column = "create_date", property = "createDate", jdbcType = JdbcType.TIMESTAMP),
            @Result(column = "update_by", property = "updateBy", jdbcType = JdbcType.BIGINT),
            @Result(column = "update_date", property = "updateDate", jdbcType = JdbcType.TIMESTAMP),
            @Result(column = "remarks", property = "remarks", jdbcType = JdbcType.VARCHAR),
            @Result(column = "del_flag", property = "delFlag", jdbcType = JdbcType.CHAR),
            @Result(column = "institution_id", property = "institutionId", jdbcType = JdbcType.BIGINT),
            @Result(column = "roster_id", property = "rosterId", jdbcType = JdbcType.BIGINT),
            @Result(column = "expire_date", property = "expireDate", jdbcType = JdbcType.DATE),
            @Result(column = "start_date", property = "startDate", jdbcType = JdbcType.DATE),
            @Result(column = "fee", property = "fee", jdbcType = JdbcType.DECIMAL)
    })
    List<ExpireManagement> listByRosterId(@Param("rosterId") Long rosterId, @Param("institutionId") Long institutionId, @Param("index") int index, @Param("size") int size);

    @SelectProvider(type = ExpireManagementSqlProvider.class, method = "listLatestByInstitutionId")
    @Results({
            @Result(column = "id", property = "id", jdbcType = JdbcType.BIGINT, id = true),
            @Result(column = "roster_id", property = "rosterId", jdbcType = JdbcType.BIGINT),
            @Result(column = "roster_name", property = "rosterName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "expire_date", property = "expireDate", jdbcType = JdbcType.DATE),
            @Result(column = "sms_date", property = "smsDate", jdbcType = JdbcType.DATE),
            @Result(column = "roster_img", property = "rosterImg", jdbcType = JdbcType.VARCHAR),
            @Result(column = "year", property = "year", jdbcType = JdbcType.BIGINT),
            @Result(column = "grade", property = "grade", jdbcType = JdbcType.VARCHAR)
    })
    List<RosterExpireManagementLatestDto> listLatestByInstitutionId(@Param("institutionId") Long institutionId,
                                                                    @Param("year") Long year,
                                                                    @Param("index") int index,
                                                                    @Param("size") int size);

    @SelectProvider(type = ExpireManagementSqlProvider.class, method = "listLatestByInstitutionIdOth")
    @Results({
            @Result(column = "id", property = "id", jdbcType = JdbcType.BIGINT, id = true),
            @Result(column = "roster_id", property = "rosterId", jdbcType = JdbcType.BIGINT),
            @Result(column = "roster_name", property = "rosterName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "expire_date", property = "expireDate", jdbcType = JdbcType.DATE),
            @Result(column = "sms_date", property = "smsDate", jdbcType = JdbcType.DATE),
            @Result(column = "roster_img", property = "rosterImg", jdbcType = JdbcType.VARCHAR),
            @Result(column = "year", property = "year", jdbcType = JdbcType.BIGINT),
            @Result(column = "grade", property = "grade", jdbcType = JdbcType.VARCHAR)
    })
    List<RosterExpireManagementLatestDto> listLatestByInstitutionIdOth(@Param("institutionId") Long institutionId,
                                                                       @Param("startYear") Long startYear,
                                                                       @Param("endYear") Long endYear,
                                                                       @Param("index") int index,
                                                                       @Param("size") int size);

    @Select({
            "select",
            "b.* ",
            "from wt_expire_management_latest a",
            "join wt_expire_management b on a.expire_management_id = b.id",
            "where b.roster_id = #{rosterId,jdbcType=BIGINT} and b.institution_id = #{institutionId}"
    })
    @Results({
            @Result(column = "id", property = "id", jdbcType = JdbcType.BIGINT, id = true),
            @Result(column = "create_by", property = "createBy", jdbcType = JdbcType.BIGINT),
            @Result(column = "create_date", property = "createDate", jdbcType = JdbcType.TIMESTAMP),
            @Result(column = "update_by", property = "updateBy", jdbcType = JdbcType.BIGINT),
            @Result(column = "update_date", property = "updateDate", jdbcType = JdbcType.TIMESTAMP),
            @Result(column = "remarks", property = "remarks", jdbcType = JdbcType.VARCHAR),
            @Result(column = "del_flag", property = "delFlag", jdbcType = JdbcType.CHAR),
            @Result(column = "institution_id", property = "institutionId", jdbcType = JdbcType.BIGINT),
            @Result(column = "roster_id", property = "rosterId", jdbcType = JdbcType.BIGINT),
            @Result(column = "expire_date", property = "expireDate", jdbcType = JdbcType.DATE),
            @Result(column = "start_date", property = "startDate", jdbcType = JdbcType.DATE),
            @Result(column = "fee", property = "fee", jdbcType = JdbcType.DECIMAL)
    })
    ExpireManagement getByRosterIdAndInstitutionIdOrderByExpireDate(@Param("rosterId") Long rosterId, @Param("institutionId") Long institutionId);

    @Select({
            "select",
            "* ",
            "from wt_expire_management",
            "where del_flag = '1' and institution_id = #{institutionId} and roster_id = #{rosterId}",
            "order by expire_date desc, id desc limit 1"
    })
    @Results({
            @Result(column = "id", property = "id", jdbcType = JdbcType.BIGINT, id = true),
            @Result(column = "create_by", property = "createBy", jdbcType = JdbcType.BIGINT),
            @Result(column = "create_date", property = "createDate", jdbcType = JdbcType.TIMESTAMP),
            @Result(column = "update_by", property = "updateBy", jdbcType = JdbcType.BIGINT),
            @Result(column = "update_date", property = "updateDate", jdbcType = JdbcType.TIMESTAMP),
            @Result(column = "remarks", property = "remarks", jdbcType = JdbcType.VARCHAR),
            @Result(column = "del_flag", property = "delFlag", jdbcType = JdbcType.CHAR),
            @Result(column = "institution_id", property = "institutionId", jdbcType = JdbcType.BIGINT),
            @Result(column = "roster_id", property = "rosterId", jdbcType = JdbcType.BIGINT),
            @Result(column = "expire_date", property = "expireDate", jdbcType = JdbcType.DATE),
            @Result(column = "start_date", property = "startDate", jdbcType = JdbcType.DATE),
            @Result(column = "fee", property = "fee", jdbcType = JdbcType.DECIMAL)
    })
    ExpireManagement getOneOrderByExpireDateDesc(@Param("institutionId") Long institutionId, @Param("rosterId") Long rosterId);
}